Privacy-aware analytics is the practice of generating insights without exposing PII, helping you stay compliant with laws like GDPR. The key is to design your data layer so sensitive information is protected by default – using techniques like masking, aggregation, and pseudonymization. In this guide, you’ll learn four practical SQL methods to analyze data safely without risking compliance breaches.
Whether you’re based in Europe or the United States, you must adhere to strict privacy laws. These laws, such as GDPR, consist of strict legal rules that organizations must follow. If they don’t, they risk receiving monetary fines of dizzying proportions. Alongside that, there’s reputation on the line.
Put simply: the consequences of non-compliance are severe, so handling data comes with a hefty legal and moral responsibility.
The three core steps in handling data typically include processing, storing, and transmitting the data – but what about the analytics? The C-level wants to understand what is happening, marketers need to pull specific data to run a campaign…basically, analytics are important.
So, how do you present data without exposing PII (personally identifiable information) and, in turn, without violating these strict privacy laws?
In this article, I’ll explain, step-by-step, four effective methods of building a privacy-aware analytics layer with SQL.
What are privacy-aware analytics – and why are they important?
Though this is not a legal guide (nor legal advice), I will give you my perspective on how to handle analytics safely. This comes from my experience as a full-stack developer (back-end focused), and what I’ve seen in the real world.
But first – what actually are privacy-aware analytics? This is not just a case of compliance ‘theater’ (check dozens of boxes, sign processing agreements, etc)… In reality, many seemingly non-identifiable pieces of information can be combined to construct a personally identifiable set. For example, take:
- A timestamp
- Coarse location (from an IP)
- User agent
- Session data
With this information, you have a unique fingerprint.
How is a fingerprint generated?
A fingerprint is generated through specific technical characteristics collected by a browser. These include the user agent, the operating system (OS) being used, the underlying browser specs, and more that you can learn about here.
The above combinatorial pattern is traced down the SQL stack. Raw event tables capture more than you see on a dashboard. Joined datasets pull identifiers from one system into another. But are they supposed to?
A user_id should not be placed near a free-text support_note from the customer relationship management (CRM) system. If this happens, contractor data, BI extracts – the critical data – can easily escape your control.
GDPR Article 25 calls this out directly. Data protection by design and by default. The obligation is not to clean things when the audit is due, but to architect the pipeline so sensitive data never lands where it’s not supposed to.
The three data classification tiers explained
You need to know what you are querying before writing a single analytics query. It sounds obvious, but I’ve seen in practice that the sensitivity of a column may only be discovered after things go south. Classification is the cheap and boring step that prevents the expensive, headline-catching incidents.
Let’s focus on three data classification tiers:
- Direct personal identifiers – fields for a specific person, on their own (email, full name, phone, account number, etc.)
- Quasi-identifiers – usually harmless on their own, but can be used to identify an individual combined. This is the fingerprinting problem I’ve mentioned above.
- Non-PII – aggregates and properties that cannot be tied back to a person.
The sole reason why this has to happen before writing queries (not after, or during a review), is because SQL does not care. JOIN will, without question, combine a “non-PII” table with a “direct identifier” user table, and produce a result set inheriting the highest sensitivity of its input data.
The practical implementation of classification metadata (before the PII lands on a sales meeting) is a column_registry table (or whatever your warehouse calls its metadata layer). At a minimum, you want something like this:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE column_registry ( schema_name TEXT NOT NULL, table_name TEXT NOT NULL, column_name TEXT NOT NULL, sensitivity TEXT NOT NULL, -- 'direct', 'quasi', 'non_pii' pii_category TEXT, -- 'email', 'name', 'location', etc. lawful_basis TEXT, -- why you're allowed to hold this retention_days INTEGER, notes TEXT, PRIMARY KEY (schema_name, table_name, column_name) ); |
You can lint data build tool (dbt) models against it, block deploys that introduce unclassified columns, and enforce masking policies instead of blatantly querying data. The registry is the precursor to masking and aggregation, and is enforceable.
Protect sensitive data with Redgate Test Data Manager
How to protect sensitive data: 4 top techniques explained
Now you know exactly what data is sensitive (generally speaking), you have four main techniques for keeping that data protected. None of them are exclusive – you can layer them or combine them.
Pseudonymization
Pseudonymization works by replacing personal identifiers with reversible placeholders stored separately under strict controls. It allows data to be analyzed without exposing PII, while still enabling re-identification when necessary.
Pseudonymization involves creating separate tables in the database, consisting of:
- Removing personal identifiers from the original dataset
- Replacing such identifiers with non-related placeholder values (aka pseudonyms)
- Storing mappings between real identifiers and pseudonyms, under strict access controls
You may also be interested in…
Pseudonymization and the Inference Attack
This practice is encouraged by the EU’s GDPR relating to the secure storage of PII data. I should note that pseudonymization is not anonymization. Anonymization completely anonymizes the data. Identifiable pieces of information are stripped completely, and you cannot restore the original data.
Pseudonymization, on the other hand, is reversible by design. The original identifiable data can be restored if you have the mapping table. Thus, you can process or analyze records without exposing PII – and when you have a legitimate reason to trace back to the source data, you can do that as well.
Aggregation floors
Sometimes, a dataset is so small that you can trace the data directly to one or a few individuals. To prevent this, can you set a minimum group size whereby anything below this size will not return any data. These are known as ‘aggregation floors.’
The US Census Bureau documented this problem a long time ago. Published data can be vulnerable to reconstruction attacks, where combining aggregated information allows an outsider to infer confidential details about an individual.
The Bureau developed an entire disclosure avoidance program, consisting of ‘cell suppression.’ What this means is, when the cell contains too few people, it’s completely withheld from publication.
How to add aggregation floors to a SQL query
|
1 2 3 4 5 6 7 8 9 10 |
SELECT country, plan_tier, DATE_TRUNC('week', created_at) AS signup_week, CASE WHEN COUNT(*) < 10 THEN NULL ELSE COUNT(*) END AS user_count FROM users GROUP BY 1, 2, 3; |
The threshold is not fixed, so ten is a reasonable starting point for most analytics cases. A regulated industry might have different standards regarding this threshold, however.
Important note!
Suppression != rounding – Changing the count 3 with < 10 is OK, but returning 10 is fabricating data.
Column-level masking
Column-level masking protects sensitive data by transforming specific fields (rather than removing entire rows) so queries still return useful, but non-identifying, information.
To apply column-level masking, you can use views. You need to define the masking logic once at the view layer and everything querying through the view receives the sanitized version.
|
1 2 3 4 5 6 7 8 9 |
CREATE VIEW users_masked AS SELECT user_id, CONCAT(LEFT(email, 2), '****@****.com') AS email, LEFT(phone, 3) || '****' AS phone, DATE_TRUNC('year', date_of_birth) AS birth_year, country, plan_tier FROM users; |
The goal of column-level masking is to return the minimum fidelity needed for the query to return useful data.
The masking approach depends on the column type:
- Free-text fields (
support_notes,bio,address) are very risky. Partial masking is not enough because you cannot predict what’s in the field. You should redact or exclude entirely, unless there’s a specific reason to expose them. - Structured identifiers (
email,phone) can work well with partial masking. You preserve just enough for operational use, for example the first two characters of an email, or the area code of a phone number. - Dates should be truncated to the least precise unit the query actually needs. Birth year instead of birth date, month instead of a specific timestamp, and so forth.
- Numeric values (
salary,account_balance) are better off bucketed into ranges than masked. A masked salary field is not useful to anyone.
One thing to get right from the start: masking at the view layer only makes sense if the direct table access is locked down. If an analyst can SELECT * FROM users, that view becomes obsolete. Column-level masking needs access control just as SQL does.
Row-level security
Row-level security allows you to control exactly which rows a user can see. When applied, it is enforced automatically on every query, regardless of who wrote it.
The support team, regional managers, financial guys, and the C-level might all operate an orders table, but it may not be appropriate for all of them to be able to see the same rows. Without row-level security, this boundary is enforced in the backend.
With row-level security, however, responsibility is shifted to the database. Here, it’s enforced automatically on every query, regardless of who wrote it.
|
1 2 3 4 5 6 |
-- Regional managers see only their region CREATE POLICY regional_manager_policy ON orders FOR SELECT USING ( region = current_setting('app.current_user_region') ); |
- Superusers bypass RLS by default in PostgreSQL. Your application’s database role should not be a superuser. Use a dedicated role with minimum privileges and verify that policies apply to it.
- Policies compose. When there is more than one policy, PostgreSQL applies them with
ORby default.
The structural patterns of these four techniques
The four techniques I’ve covered above are ideally not used in isolation. Rather, you use them in combination. The golden approach involves organizing the data into three layers:
- Raw
- Pseudonymized
- Aggregated
PII lands in the raw layer and stays there. The layer beneath receives pseudonymized records, with real identifiers replaced and the mapping table locked down separately. The next layer exposes only aggregates, with ‘aggregate floors’ in place.
Elsewhere, identity resolution and behavioral analytics should live in separate schemas, with separate access roles.
Audit logging on sensitive columns (and in general!) is the cherry on top of everything. This gives you visibility into who queried what, and when.
Protecting sensitive data: what to avoid
You should explicitly avoid quasi-identifier combinations that re-identify anonymized rows, and avoid overusing SELECT* in SQL VIEWs.
Back in 2006, Netflix learned – the hard way – why you should avoid quasi-identifier combinations that re-identify anonymized rows.
They ran a competition to find the best collaborative filtering algorithm to predict user ratings for films, based on previous ratings. To enable this, they released an anonymized dataset of 100 million movie ratings from 480,000 customers.
Researchers Arvind Narayanan and Vitaly Shmatikov then successfully cross-referenced the dataset with public IMDb reviews and identified individual users from the combination of ratings and timestamps alone. Yikes.
Overusing SELECT * in views is another no-no. A view is only as safe as the columns it exposes. SELECT * means that any new column added to the table (including sensitive ones) are automatically surfaced.
Technicalities aside, following GDPR is one thing, but fulfilling the requirements is another. You should not forget deletion cascades on right-to-erasure requests (GDPR Article 17), whereby a user’s data must deleted upon request. If the pseudonymization mapping table holds a reference to a deleted user’s real identifier, you have not satisfied the request.
Conclusion: the importance of privacy-aware analytics
Getting your analytics to the point where they are privacy-aware is the result of early architectural decisions. The four techniques I’ve covered here are not a checklist, but layers of a privacy stack. Combined, they lead to privacy-aware analytics.
Ultimately, what differentiates a privacy breach and a privacy program is not just the sophistication of the attack, but whether protective layers were in place before the data was breached. Getting your analytics to the point where they are privacy-aware is the result of early architectural decisions, like the four techniques I’ve covered in this article.
These techniques are not just a checklist, but make up the layers of a privacy stack. Combined, they lead to privacy-aware analytics – and the assurance that you’re complying to the strict laws and regulations of today.
Simple Talk is brought to you by Redgate Software
FAQs: Privacy-aware analytics
1. What are privacy-aware analytics?
Privacy-aware analytics is the practice of analyzing data while preventing exposure of personally identifiable information (PII), using techniques like masking, aggregation, and pseudonymization.
2. Why are privacy-aware analytics important?
They help organizations comply with regulations like GDPR, avoid fines, and protect user trust by ensuring sensitive data isn’t exposed.
3. What counts as PII?
Direct identifiers (e.g., email, phone number), and quasi-identifiers (e.g., IP, timestamps) that can be combined to identify individuals.
4. Is pseudonymization the same as anonymization?
No. Pseudonymization is reversible with a mapping table; anonymization is permanent and cannot be reversed.
5. What is an aggregation floor?
A minimum group size (e.g., 10 users) required before data is shown, preventing identification in small datasets.
6. How does SQL help protect sensitive data?
SQL enables techniques like column masking, aggregation, and row-level security to control what data is exposed.
7. What is the biggest mistake to avoid?
Combining datasets in ways that re-identify users—especially through joins or exposing too many columns (e.g., using SELECT *).
8. Do I need all protection techniques at once?
Not necessarily, but combining multiple layers (masking, aggregation, access control) provides the strongest protection.
Load comments